﻿using Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace Dal
{
    public class CouponDAL
    {
        private static readonly string CouponConnectionString = ConfigurationManager.ConnectionStrings["YmatouCouponConnectionString"].ConnectionString;

        /// <summary>
        /// 获取优惠券批次内容
        /// </summary>
        /// <param name="batchId"></param>
        /// <returns></returns>
        public static CouponBatch GetCouponBatch(int batchId)
        {
            CouponBatch result = null;
            using (IDbConnection conn = new SqlConnection(CouponConnectionString))
            {
                string query = @"SELECT BatchId=a.iBatchId,CouponSettingId=a.iCouponSettingId,AddTime=a.dAddTime,a.ImpresetInvoiceId,CouponTotalNum=a.iCouponTotalNum,BatchCreateType=a.iBatchCreateType,CouponName=a.sCouponName,BatchPrefix=a.sBatchPrefix,InvalidTime=a.dInvalidTime,InvalidStatus=a.bInvalidStatus,OperatorId=a.iOperatorId,SendType=a.iSendType,BatchCode=a.sBatchCode,ExpiredDate=a.dExpiredDate,Usage=a.iUsage,CouponNumPerUser=a.iCouponNumPerUser,a.ApplyDepartment,a.InvalidUser,InvalidMemo
                                ,ValidEnd=b.dValidEnd,MaxUseTimePerUser=b.iMaxUseTimePerUser,ApplyUser=b.sApplyUser,ApplyMemo=b.sApplyMemo,UseTotalCount=b.iUseTotalCount,CouponUseCount=b.iCouponUseCount,EffectiveType=b.iEffectiveType,EffectiveValidType=b.iEffectiveValidType,EffectiveDays=b.iEffectiveDays,ValidStart=b.dValidStart,CouponUseType=b.iCouponUseType,MaxUseTime=b.iMaxUseTime
                                ,SpecificProducts=c.sSpecificProducts,UserType=c.iUserType,StockStatus=c.sStockStatus,UsePlatforms=c.sUsePlatforms,SellerIds=c.sSellerIds,ActivityIds=c.sActivityIds
                                FROM dbo.Ymt_CouponBatch AS a WITH(NOLOCK)
                                LEFT JOIN dbo.Ymt_CouponSetting AS b WITH(NOLOCK) ON a.iCouponSettingId=b.iCouponSettingId
                                LEFT JOIN dbo.Ymt_CouponScenario AS c WITH(NOLOCK) ON b.iScenarioId=c.iCouponScenarioId
                                WHERE iBatchId=@BatchID";
                result = conn.Query<CouponBatch>(query, new { BatchID = batchId }).SingleOrDefault();
            }
            return result;
        }

        /// <summary>
        /// 获取优惠券批次内容
        /// </summary>
        /// <param name="batchIds"></param>
        /// <returns></returns>
        public static List<CouponBatch> GetCouponBatchs(List<int> batchIds)
        {
            List<CouponBatch> result = null;
            using (IDbConnection conn = new SqlConnection(CouponConnectionString))
            {
                SQLTools tool = new SQLTools();
                for (int i = 0; i < batchIds.Count; i++)
                {
                    var id = batchIds[i];
                    tool.AddSqlParameter("p" + i, SqlDbType.Int, id);
                }
                string ids = string.Join("','", batchIds); //tool.GetParametersNames();

                string query = @"SELECT BatchId=a.iBatchId,CouponSettingId=a.iCouponSettingId,AddTime=a.dAddTime,a.ImpresetInvoiceId,CouponTotalNum=a.iCouponTotalNum,BatchCreateType=a.iBatchCreateType,CouponName=a.sCouponName,BatchPrefix=a.sBatchPrefix,InvalidTime=a.dInvalidTime,InvalidStatus=a.bInvalidStatus,OperatorId=a.iOperatorId,SendType=a.iSendType,BatchCode=a.sBatchCode,ExpiredDate=a.dExpiredDate,Usage=a.iUsage,CouponNumPerUser=a.iCouponNumPerUser,a.ApplyDepartment,a.InvalidUser,InvalidMemo
                                ,ValidEnd=b.dValidEnd,MaxUseTimePerUser=b.iMaxUseTimePerUser,ApplyUser=b.sApplyUser,ApplyMemo=b.sApplyMemo,UseTotalCount=b.iUseTotalCount,CouponUseCount=b.iCouponUseCount,EffectiveType=b.iEffectiveType,EffectiveValidType=b.iEffectiveValidType,EffectiveDays=b.iEffectiveDays,ValidStart=b.dValidStart,CouponUseType=b.iCouponUseType,MaxUseTime=b.iMaxUseTime
                                ,SpecificProducts=c.sSpecificProducts,UserType=c.iUserType,StockStatus=c.sStockStatus,UsePlatforms=c.sUsePlatforms,SellerIds=c.sSellerIds,ActivityIds=c.sActivityIds
                                FROM dbo.Ymt_CouponBatch AS a WITH(NOLOCK)
                                LEFT JOIN dbo.Ymt_CouponSetting AS b WITH(NOLOCK) ON a.iCouponSettingId=b.iCouponSettingId
                                LEFT JOIN dbo.Ymt_CouponScenario AS c WITH(NOLOCK) ON b.iScenarioId=c.iCouponScenarioId
                                WHERE iBatchId in('" + ids + "')";
                result = conn.Query<CouponBatch>(query).ToList();//, tool.GetParameters()
            }
            return result;
        }

        /// <summary>
        /// 获取优惠券批次内容
        /// </summary>
        /// <param name="batchIds"></param>
        /// <returns></returns>
        public static List<CouponBatch> GetCouponBatchs()
        {
            List<CouponBatch> result = null;
            using (IDbConnection conn = new SqlConnection(CouponConnectionString))
            {
                string query = @"SELECT BatchId=a.iBatchId,CouponSettingId=a.iCouponSettingId,AddTime=a.dAddTime,a.ImpresetInvoiceId,CouponTotalNum=a.iCouponTotalNum,BatchCreateType=a.iBatchCreateType,CouponName=a.sCouponName,BatchPrefix=a.sBatchPrefix,InvalidTime=a.dInvalidTime,InvalidStatus=a.bInvalidStatus,OperatorId=a.iOperatorId,SendType=a.iSendType,BatchCode=a.sBatchCode,ExpiredDate=a.dExpiredDate,Usage=a.iUsage,CouponNumPerUser=a.iCouponNumPerUser,a.ApplyDepartment,a.InvalidUser,InvalidMemo
                                ,ValidEnd=b.dValidEnd,MaxUseTimePerUser=b.iMaxUseTimePerUser,ApplyUser=b.sApplyUser,ApplyMemo=b.sApplyMemo,UseTotalCount=b.iUseTotalCount,CouponUseCount=b.iCouponUseCount,EffectiveType=b.iEffectiveType,EffectiveValidType=b.iEffectiveValidType,EffectiveDays=b.iEffectiveDays,ValidStart=b.dValidStart,CouponUseType=b.iCouponUseType,MaxUseTime=b.iMaxUseTime
                                ,SpecificProducts=c.sSpecificProducts,UserType=c.iUserType,StockStatus=c.sStockStatus,UsePlatforms=c.sUsePlatforms,SellerIds=c.sSellerIds,ActivityIds=c.sActivityIds
                                FROM dbo.Ymt_CouponBatch AS a WITH(NOLOCK)
                                LEFT JOIN dbo.Ymt_CouponSetting AS b WITH(NOLOCK) ON a.iCouponSettingId=b.iCouponSettingId
                                LEFT JOIN dbo.Ymt_CouponScenario AS c WITH(NOLOCK) ON b.iScenarioId=c.iCouponScenarioId
                                WHERE dAddTime>'"+DateTime.Now.AddDays(-2)+"'";
                result = conn.Query<CouponBatch>(query).ToList();//, tool.GetParameters()
            }
            return result;
        }

    }
}
